<!DOCTYPE html>
<html lang="en-us">
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
    
<meta charset="UTF-8">
<title>Dealing with Null Values | Elasticsearch: The Definitive Guide [2.x] | Elastic</title>
<link rel="home" href="index.html" title="Elasticsearch: The Definitive Guide [2.x]">
<link rel="up" href="structured-search.html" title="Structured Search">
<link rel="prev" href="_ranges.html" title="Ranges">
<link rel="next" href="filter-caching.html" title="All About Caching">
<meta name="DC.type" content="Learn/Docs/Legacy/Elasticsearch/Definitive Guide/2.x">
<meta name="DC.subject" content="Elasticsearch">
<meta name="DC.identifier" content="2.x">
<meta name="robots" content="noindex,nofollow">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <script src="https://cdn.optimizely.com/js/18132920325.js"></script>
    <link rel="apple-touch-icon" sizes="57x57" href="/apple-icon-57x57.png">
    <link rel="apple-touch-icon" sizes="60x60" href="/apple-icon-60x60.png">
    <link rel="apple-touch-icon" sizes="72x72" href="/apple-icon-72x72.png">
    <link rel="apple-touch-icon" sizes="76x76" href="/apple-icon-76x76.png">
    <link rel="apple-touch-icon" sizes="114x114" href="/apple-icon-114x114.png">
    <link rel="apple-touch-icon" sizes="120x120" href="/apple-icon-120x120.png">
    <link rel="apple-touch-icon" sizes="144x144" href="/apple-icon-144x144.png">
    <link rel="apple-touch-icon" sizes="152x152" href="/apple-icon-152x152.png">
    <link rel="apple-touch-icon" sizes="180x180" href="/apple-icon-180x180.png">
    <link rel="icon" type="image/png" href="/favicon-32x32.png" sizes="32x32">
    <link rel="icon" type="image/png" href="/android-chrome-192x192.png" sizes="192x192">
    <link rel="icon" type="image/png" href="/favicon-96x96.png" sizes="96x96">
    <link rel="icon" type="image/png" href="/favicon-16x16.png" sizes="16x16">
    <link rel="manifest" href="/manifest.json">
    <meta name="apple-mobile-web-app-title" content="Elastic">
    <meta name="application-name" content="Elastic">
    <meta name="msapplication-TileColor" content="#ffffff">
    <meta name="msapplication-TileImage" content="/mstile-144x144.png">
    <meta name="theme-color" content="#ffffff">
    <meta name="naver-site-verification" content="936882c1853b701b3cef3721758d80535413dbfd">
    <meta name="yandex-verification" content="d8a47e95d0972434">
    <meta name="localized" content="true">
    <meta name="st:robots" content="follow,index">
    <meta property="og:image" content="https://www.elastic.co/static/images/elastic-logo-200.png">
    <link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">
    <link rel="icon" href="/favicon.ico" type="image/x-icon">
    <link rel="apple-touch-icon-precomposed" sizes="64x64" href="/favicon_64x64_16bit.png">
    <link rel="apple-touch-icon-precomposed" sizes="32x32" href="/favicon_32x32.png">
    <link rel="apple-touch-icon-precomposed" sizes="16x16" href="/favicon_16x16.png">
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
    <link rel="stylesheet" type="text/css" href="/guide/static/styles.css">
  </head>

  <!--© 2015-2021 Elasticsearch B.V. Copying, publishing and/or distributing without written permission is strictly prohibited.-->

  <body>
    <!-- Google Tag Manager -->
    <script>dataLayer = [];</script><noscript><iframe src="//www.googletagmanager.com/ns.html?id=GTM-58RLH5" height="0" width="0" style="display:none;visibility:hidden"></iframe></noscript>
    <script>(function(w,d,s,l,i){w[l]=w[l]||[];w[l].push({'gtm.start': new Date().getTime(),event:'gtm.js'});var f=d.getElementsByTagName(s)[0], j=d.createElement(s),dl=l!='dataLayer'?'&l='+l:'';j.async=true;j.src= '//www.googletagmanager.com/gtm.js?id='+i+dl;f.parentNode.insertBefore(j,f); })(window,document,'script','dataLayer','GTM-58RLH5');</script>
    <!-- End Google Tag Manager -->

    <!-- Global site tag (gtag.js) - Google Analytics -->
    <script async src="https://www.googletagmanager.com/gtag/js?id=UA-12395217-16"></script>
    <script>
      window.dataLayer = window.dataLayer || [];
      function gtag(){dataLayer.push(arguments);}
      gtag('js', new Date());
      gtag('config', 'UA-12395217-16');
    </script>

    <!--BEGIN QUALTRICS WEBSITE FEEDBACK SNIPPET-->
    <script type="text/javascript">
      (function(){var g=function(e,h,f,g){
      this.get=function(a){for(var a=a+"=",c=document.cookie.split(";"),b=0,e=c.length;b<e;b++){for(var d=c[b];" "==d.charAt(0);)d=d.substring(1,d.length);if(0==d.indexOf(a))return d.substring(a.length,d.length)}return null};
      this.set=function(a,c){var b="",b=new Date;b.setTime(b.getTime()+6048E5);b="; expires="+b.toGMTString();document.cookie=a+"="+c+b+"; path=/; "};
      this.check=function(){var a=this.get(f);if(a)a=a.split(":");else if(100!=e)"v"==h&&(e=Math.random()>=e/100?0:100),a=[h,e,0],this.set(f,a.join(":"));else return!0;var c=a[1];if(100==c)return!0;switch(a[0]){case "v":return!1;case "r":return c=a[2]%Math.floor(100/c),a[2]++,this.set(f,a.join(":")),!c}return!0};
      this.go=function(){if(this.check()){var a=document.createElement("script");a.type="text/javascript";a.src=g;document.body&&document.body.appendChild(a)}};
      this.start=function(){var a=this;window.addEventListener?window.addEventListener("load",function(){a.go()},!1):window.attachEvent&&window.attachEvent("onload",function(){a.go()})}};
      try{(new g(100,"r","QSI_S_ZN_emkP0oSe9Qrn7kF","https://znemkp0ose9qrn7kf-elastic.siteintercept.qualtrics.com/WRSiteInterceptEngine/?Q_ZID=ZN_emkP0oSe9Qrn7kF")).start()}catch(i){}})();
    </script><div id="ZN_emkP0oSe9Qrn7kF"><!--DO NOT REMOVE-CONTENTS PLACED HERE--></div>
    <!--END WEBSITE FEEDBACK SNIPPET-->

    <div id="elastic-nav" style="display:none;"></div>
    <script src="https://www.elastic.co/elastic-nav.js"></script>

    <!-- Subnav -->
    <div>
      <div>
        <div class="tertiary-nav d-none d-md-block">
          <div class="container">
            <div class="p-t-b-15 d-flex justify-content-between nav-container">
              <div class="breadcrum-wrapper"><span><a href="/guide/" style="font-size: 14px; font-weight: 600; color: #000;">Docs</a></span></div>
            </div>
          </div>
        </div>
      </div>
    </div>

    <div class="main-container">
      <section id="content">
        <div class="content-wrapper">

          <section id="guide" lang="en">
            <div class="container">
              <div class="row">
                <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                  <!-- start body -->
                  <div class="page_header">
<p>
  <strong>WARNING</strong>: The 2.x versions of Elasticsearch have passed their
  <a href="https://www.elastic.co/support/eol">EOL dates</a>. If you are running
  a 2.x version, we strongly advise you to upgrade.
</p>
<p>
  This documentation is no longer maintained and may be removed. For the latest
  information, see the <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html">current
  Elasticsearch documentation</a>.
</p>
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch: The Definitive Guide [2.x]</a></span>
»
<span class="breadcrumb-link"><a href="search-in-depth.html">Search in Depth</a></span>
»
<span class="breadcrumb-link"><a href="structured-search.html">Structured Search</a></span>
»
<span class="breadcrumb-node">Dealing with Null Values</span>
</div>
<div class="navheader">
<span class="prev">
<a href="_ranges.html">« Ranges</a>
</span>
<span class="next">
<a href="filter-caching.html">All About Caching »</a>
</span>
</div>
<div class="section">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="_dealing_with_null_values"></a>Dealing with Null Values<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch-definitive-guide/edit/2.x/080_Structured_Search/30_existsmissing.asciidoc">edit</a>
</h2>
</div></div></div>
<p>Think back to our earlier example, where documents have a field named <code class="literal">tags</code>.
This is a multivalue field.
A document may have one tag, many tags, or potentially no tags at all. If a field has
no values, how is it stored in an inverted index?</p>
<p>That’s a trick question, because the answer is: it isn’t stored at all. Let’s
look at that inverted index from the previous section:</p>
<div class="informaltable">
<table border="1" cellpadding="4px" width="50%">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<tbody>
<tr>
<td align="left" valign="top"><p>Token</p></td>
<td align="left" valign="top"><p>DocIDs</p></td>
</tr>
<tr>
<td align="left" valign="top"><p><code class="literal">open_source</code></p></td>
<td align="left" valign="top"><p><code class="literal">2</code></p></td>
</tr>
<tr>
<td align="left" valign="top"><p><code class="literal">search</code></p></td>
<td align="left" valign="top"><p><code class="literal">1</code>,<code class="literal">2</code></p></td>
</tr>
</tbody>
</table>
</div>
<p>How would you store a field that doesn’t exist in that data structure?  You
can’t!  An inverted index is simply a list of tokens and the documents that
contain them.  If a field doesn’t exist, it doesn’t hold any tokens, which
means it won’t be represented in an inverted index data structure.</p>
<p>Ultimately, this means that a <code class="literal">null</code>, <code class="literal">[]</code> (an empty
array), and <code class="literal">[null]</code> are all equivalent. They simply don’t exist in the
inverted index!</p>
<p>Obviously, the world is not simple, and data is often missing fields, or contains
explicit nulls or empty arrays. To deal with these situations, Elasticsearch has
a few tools to work with null or missing values.</p>
<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="_exists_query"></a>exists Query<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch-definitive-guide/edit/2.x/080_Structured_Search/30_existsmissing.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The first tool in your arsenal is the <code class="literal">exists</code> query.
  This query will return documents that have any value in
the specified field. Let’s use the tagging example and index some example documents:</p>
<div class="pre_wrapper lang-sense">
<pre class="programlisting prettyprint lang-sense">POST /my_index/posts/_bulk
{ "index": { "_id": "1"              }}
{ "tags" : ["search"]                }  <a id="CO53-1"></a><i class="conum" data-value="1"></i>
{ "index": { "_id": "2"              }}
{ "tags" : ["search", "open_source"] }  <a id="CO53-2"></a><i class="conum" data-value="2"></i>
{ "index": { "_id": "3"              }}
{ "other_field" : "some data"        }  <a id="CO53-3"></a><i class="conum" data-value="3"></i>
{ "index": { "_id": "4"              }}
{ "tags" : null                      }  <a id="CO53-4"></a><i class="conum" data-value="4"></i>
{ "index": { "_id": "5"              }}
{ "tags" : ["search", null]          }  <a id="CO53-5"></a><i class="conum" data-value="5"></i></pre>
</div>
<div class="sense_widget" data-snippet="snippets/080_Structured_Search/30_Exists_missing.json"></div>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO53-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>The <code class="literal">tags</code> field has one value.</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO53-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>The <code class="literal">tags</code> field has two values.</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO53-3"><i class="conum" data-value="3"></i></a></p>
</td>
<td align="left" valign="top">
<p>The <code class="literal">tags</code> field is missing altogether.</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO53-4"><i class="conum" data-value="4"></i></a></p>
</td>
<td align="left" valign="top">
<p>The <code class="literal">tags</code> field is set to <code class="literal">null</code>.</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO53-5"><i class="conum" data-value="5"></i></a></p>
</td>
<td align="left" valign="top">
<p>The <code class="literal">tags</code> field has one value and a <code class="literal">null</code>.</p>
</td>
</tr>
</table>
</div>
<p>The resulting inverted index for our <code class="literal">tags</code> field will look like this:</p>
<div class="informaltable">
<table border="1" cellpadding="4px" width="50%">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<tbody>
<tr>
<td align="left" valign="top"><p>Token</p></td>
<td align="left" valign="top"><p>DocIDs</p></td>
</tr>
<tr>
<td align="left" valign="top"><p><code class="literal">open_source</code></p></td>
<td align="left" valign="top"><p><code class="literal">2</code></p></td>
</tr>
<tr>
<td align="left" valign="top"><p><code class="literal">search</code></p></td>
<td align="left" valign="top"><p><code class="literal">1</code>,<code class="literal">2</code>,<code class="literal">5</code></p></td>
</tr>
</tbody>
</table>
</div>
<p>Our objective is to find all documents where a tag is set.  We don’t care what
the tag is, so long as it exists within the document.  In SQL parlance,
we would use an <code class="literal">IS NOT NULL</code> query:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT tags
FROM   posts
WHERE  tags IS NOT NULL</pre>
</div>
<p>In Elasticsearch, we use the <code class="literal">exists</code> query:</p>
<div class="pre_wrapper lang-sense">
<pre class="programlisting prettyprint lang-sense">GET /my_index/posts/_search
{
    "query" : {
        "constant_score" : {
            "filter" : {
                "exists" : { "field" : "tags" }
            }
        }
    }
}</pre>
</div>
<div class="sense_widget" data-snippet="snippets/080_Structured_Search/30_Exists_missing.json"></div>
<p>Our query returns three documents:</p>
<div class="pre_wrapper lang-json">
<pre class="programlisting prettyprint lang-json">"hits" : [
    {
      "_id" :     "1",
      "_score" :  1.0,
      "_source" : { "tags" : ["search"] }
    },
    {
      "_id" :     "5",
      "_score" :  1.0,
      "_source" : { "tags" : ["search", null] } <a id="CO54-1"></a><i class="conum" data-value="1"></i>
    },
    {
      "_id" :     "2",
      "_score" :  1.0,
      "_source" : { "tags" : ["search", "open source"] }
    }
]</pre>
</div>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO54-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>Document 5 is returned even though it contains a <code class="literal">null</code> value. The field
exists because a real-value tag was indexed, so the <code class="literal">null</code> had no impact
on the filter.</p>
</td>
</tr>
</table>
</div>
<p>The results are easy to understand.  Any document that has terms in the
<code class="literal">tags</code> field was returned as a hit.  The only two documents that were excluded
were documents 3 and 4.</p>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="_missing_query"></a>missing Query<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch-definitive-guide/edit/2.x/080_Structured_Search/30_existsmissing.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The <code class="literal">missing</code> query is essentially
 the inverse of <code class="literal">exists</code>: it returns
documents where there is <em>no</em> value for a particular field, much like this
SQL:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT tags
FROM   posts
WHERE  tags IS NULL</pre>
</div>
<p>Let’s swap the <code class="literal">exists</code> query for a <code class="literal">missing</code> query from our previous example:</p>
<div class="pre_wrapper lang-sense">
<pre class="programlisting prettyprint lang-sense">GET /my_index/posts/_search
{
    "query" : {
        "constant_score" : {
            "filter": {
                "missing" : { "field" : "tags" }
            }
        }
    }
}</pre>
</div>
<div class="sense_widget" data-snippet="snippets/080_Structured_Search/30_Exists_missing.json"></div>
<p>And, as you would expect, we get back the two docs that have no real values
in the <code class="literal">tags</code> field—​documents 3 and 4:</p>
<div class="pre_wrapper lang-json">
<pre class="programlisting prettyprint lang-json">"hits" : [
    {
      "_id" :     "3",
      "_score" :  1.0,
      "_source" : { "other_field" : "some data" }
    },
    {
      "_id" :     "4",
      "_score" :  1.0,
      "_source" : { "tags" : null }
    }
]</pre>
</div>
<div class="sidebar">
<div class="titlepage"><div><div>
<p class="title"><strong>When null Means null</strong></p>
</div></div></div>
<p>Sometimes you need to be able to distinguish between a field that doesn’t have
a value, and a field that has been explicitly set to <code class="literal">null</code>. With the default
behavior that we saw previously, this is impossible; the data is lost. Luckily,
there is an option that we can set that replaces explicit  <code class="literal">null</code> values with
a <em>placeholder</em> value of our choosing.</p>
<p>When specifying the mapping for a string, numeric, Boolean, or date field, you
can also set a <code class="literal">null_value</code> that will be used whenever an explicit <code class="literal">null</code>
value is encountered.  A field without a value will still be excluded from the
inverted index.</p>
<p>When choosing a suitable <code class="literal">null_value</code>, ensure the following:</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
It matches the field’s type.  You can’t use a string <code class="literal">null_value</code> in a
field of type <code class="literal">date</code>.
</li>
<li class="listitem">
It is different from the normal values that the field may contain, to
avoid confusing real values with <code class="literal">null</code> values.
</li>
</ul>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="_existsmissing_on_objects"></a>exists/missing on Objects<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch-definitive-guide/edit/2.x/080_Structured_Search/30_existsmissing.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The <code class="literal">exists</code> and <code class="literal">missing</code> queries
also
work on inner objects, not just core types.  With the following document</p>
<div class="pre_wrapper lang-js">
<pre class="programlisting prettyprint lang-js">{
   "name" : {
      "first" : "John",
      "last" :  "Smith"
   }
}</pre>
</div>
<p>you can check for the existence of <code class="literal">name.first</code> and <code class="literal">name.last</code> but also just
<code class="literal">name</code>. However, in <a class="xref" href="mapping.html" title="Types and Mappings">Types and Mappings</a>, we said that an object like the preceding one is
flattened internally into a simple field-value structure, much like this:</p>
<div class="pre_wrapper lang-js">
<pre class="programlisting prettyprint lang-js">{
   "name.first" : "John",
   "name.last"  : "Smith"
}</pre>
</div>
<p>So how can we use an <code class="literal">exists</code> or <code class="literal">missing</code> query on the <code class="literal">name</code> field, which
doesn’t really exist in the inverted index?</p>
<p>The reason that it works is that a filter like</p>
<div class="pre_wrapper lang-js">
<pre class="programlisting prettyprint lang-js">{
    "exists" : { "field" : "name" }
}</pre>
</div>
<p>is really executed as</p>
<div class="pre_wrapper lang-js">
<pre class="programlisting prettyprint lang-js">{
    "bool": {
        "should": [
            { "exists": { "field": "name.first" }},
            { "exists": { "field": "name.last" }}
        ]
    }
}</pre>
</div>
<p>That also means that if <code class="literal">first</code> and <code class="literal">last</code> were both empty, the <code class="literal">name</code>
namespace would not exist.</p>
</div>

</div>
<div class="navfooter">
<span class="prev">
<a href="_ranges.html">« Ranges</a>
</span>
<span class="next">
<a href="filter-caching.html">All About Caching »</a>
</span>
</div>
</div>

                  <!-- end body -->
                </div>
                <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                  <div id="rtpcontainer" style="display: block;">
                    <div class="mktg-promo">
                      <h3>Most Popular</h3>
                      <ul class="icons">
                        <li class="icon-elasticsearch-white"><a href="https://www.elastic.co/webinars/getting-started-elasticsearch?baymax=default&amp;elektra=docs&amp;storm=top-video">Get Started with Elasticsearch: Video</a></li>
                        <li class="icon-kibana-white"><a href="https://www.elastic.co/webinars/getting-started-kibana?baymax=default&amp;elektra=docs&amp;storm=top-video">Intro to Kibana: Video</a></li>
                        <li class="icon-logstash-white"><a href="https://www.elastic.co/webinars/introduction-elk-stack?baymax=default&amp;elektra=docs&amp;storm=top-video">ELK for Logs &amp; Metrics: Video</a></li>
                      </ul>
                    </div>
                  </div>
                </div>
              </div>
            </div>
          </section>

        </div>


<div id="elastic-footer"></div>
<script src="https://www.elastic.co/elastic-footer.js"></script>
<!-- Footer Section end-->

      </section>
    </div>

<script src="/guide/static/jquery.js"></script>
<script type="text/javascript" src="/guide/static/docs.js"></script>
<script type="text/javascript">
  window.initial_state = {}</script>
  </body>
</html>
